package com.icss.daos;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.icss.beans.HotNewsVO;
import com.icss.beans.NewsTypeVO;

/**
 * 学子园地........
 * @author 梁海成
 *
 */
public class HotNewsDao {
	
	private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd ");
	/**
	 * 获取所有的文章
	 * @return
	 */
	public ArrayList<HotNewsVO> getAllNews(){
		String sql = "select s.*,n.typeName from StuNews s join NewsType n on s.typeID=n.typeID";
		ArrayList<HotNewsVO> list = new ArrayList<HotNewsVO>();
		Dao dao = new Dao();
		ResultSet rs = null;
		rs= dao.query(sql);
		try {
			while(rs.next()){
				HotNewsVO vo = new HotNewsVO();
				vo.setNewsContext(rs.getString("stuNewsContext"));
				vo.setNewsID(rs.getInt("stuNewsID"));
				vo.setNewsTitle(rs.getString("stuNewsTitle"));
				vo.setPublishTime(sdf.format(rs.getDate("publishTime")));
				vo.setTypeName(rs.getString("typeName"));
				list.add(vo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close(dao,rs);
		}
		return list;
	}
	
	/**
	 * 获取文章所属的栏目
	 * @return
	 */
	
	public List<NewsTypeVO> getType(){
		String sql = "select * from NewsType";
		Dao dao = new Dao();
		ResultSet rs = null;
		rs = dao.query(sql);
		List<NewsTypeVO> list = new ArrayList<NewsTypeVO>();
		try {
			while(rs.next()){
				NewsTypeVO vo = new NewsTypeVO();
				vo.setTypeID(rs.getInt("typeID"));
				vo.setTypeName(rs.getString("typeName").trim());
				vo.setRemark(rs.getString("remark"));
				list.add(vo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close(dao,rs);
		}
		return list;
	}
	
	/**
	 * 获取相应栏目下的文章
	 * @param id
	 * @return
	 */
	public List<HotNewsVO> getTypeNews(int id){
		String sql = "select * from stuNews where typeID="+id+" order by publishTime desc";
		Dao dao = new Dao();
		ResultSet rs = null;
		rs=dao.query(sql);
		List<HotNewsVO> list = new ArrayList<HotNewsVO>();
		try {
			while(rs.next()){
				HotNewsVO vo = new HotNewsVO();
				vo.setNewsID(rs.getInt("stuNewsID"));
				vo.setNewsTitle(rs.getString("stuNewsTitle"));
				vo.setPublishTime(sdf.format(rs.getDate("publishTime")));
				vo.setTypeID(rs.getInt("typeID"));
				list.add(vo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close(dao,rs);
		}
		return list;
	}
	
	/**
	 * 获取一条栏目的信息
	 * @param id
	 * @return
	 */
	public NewsTypeVO getOneNewsType(int id){
		String sql = "select * from NewsType where typeID="+id;
		Dao dao = new Dao();
		ResultSet rs = null;
		rs=dao.query(sql);
		NewsTypeVO vo = new NewsTypeVO();
		try {
			if(rs.next()){
				vo.setRemark(rs.getString("remark"));
				vo.setTypeID(rs.getInt("typeID"));
				vo.setTypeName(rs.getString("typeName"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close(dao,rs);
		}
		return vo;
	}
	
	/**
	 * 添加文章
	 * @param vo
	 */
	public void addNews(HotNewsVO vo){
		String sql ="insert into StuNews (stuNewsTitle,stuNewsContext,typeID) values('"+vo.getNewsTitle()+"','"+vo.getNewsContext()+"',"+vo.getTypeID()+")";
		Dao dao = new Dao();
		ResultSet rs = null;
		dao.execute(sql);
		close(dao,rs);
	}
	
	/**
	 * 获取一篇文章的信息
	 * @param id
	 * @return
	 */
	public HotNewsVO GetOneNews(int id){
		String sql ="select s.*,n.typeName from StuNews s join NewsType n on s.typeID=n.typeID where stuNewsID="+id;
		Dao dao = new Dao();
		ResultSet rs = null;
		rs= dao.query(sql);
		HotNewsVO vo = new HotNewsVO();
		try {
			if(rs.next()){
				vo.setNewsContext(rs.getString("stuNewsContext"));
				vo.setNewsID(rs.getInt("stuNewsID"));
				vo.setNewsTitle(rs.getString("stuNewsTitle"));
				vo.setTypeID(rs.getInt("typeID"));
				vo.setTypeName(rs.getString("typeName"));
				vo.setPublishTime(sdf.format(rs.getDate("publishTime")));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close(dao,rs);
		}
		return vo;
	}
	
	/**
	 * 更新文章
	 * @param vo
	 */
	public void updateNews(HotNewsVO vo){
		String sql = "update StuNews set stuNewsTitle='"+vo.getNewsTitle()+"',stuNewsContext='"+vo.getNewsContext()+"',typeID="+vo.getTypeID()+" where stuNewsID="+vo.getNewsID();
		Dao dao = new Dao();
		ResultSet rs = null;
		dao.execute(sql);
		close(dao,rs);
	}
	
	/**
	 * 删除文章
	 * @param id
	 */
	public void delNews(int id){
		String sql = "delete from StuNews where stuNewsID ="+id;
		Dao dao = new Dao();
		ResultSet rs = null;
		dao.execute(sql);
		close(dao,rs);
	}
	
	/**
	 * 批量删除文章
	 * @param str
	 */
	public void delAllSelect(String str){
		String[] arr = str.split(",");
		Dao dao = new Dao();
		ResultSet rs = null;
		for(int i=0;i<arr.length;i++){
			String sql = "delete from StuNews where stuNewsID="+Integer.parseInt(arr[i]);			
			dao.execute(sql);
		}
		close(dao,rs);
	}
	
	/**
	 * 获取最新的6篇文章
	 * @return
	 */
	public ArrayList<HotNewsVO> getNews(){
		String sql = "select  stuNewsTitle,stuNewsID from StuNews where typeID=1 or typeID=3 order by publishTime desc limit 0,10";
		ArrayList<HotNewsVO> list = new ArrayList<HotNewsVO>();
		Dao dao = new Dao();
		ResultSet rs = null;
		rs= dao.query(sql);
		try {
			while(rs.next()){
				HotNewsVO vo = new HotNewsVO();
				vo.setNewsID(rs.getInt("stuNewsID"));
				vo.setNewsTitle(rs.getString("stuNewsTitle"));
				list.add(vo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close(dao,rs);
		}
		return list;
	}

	
	public ArrayList<HotNewsVO> getNews2(){
		String sql = "select  stuNewsTitle,stuNewsID from StuNews where typeID=2  order by publishTime desc limit 0,8";
		ArrayList<HotNewsVO> list = new ArrayList<HotNewsVO>();
		Dao dao = new Dao();
		ResultSet rs = null;
		rs= dao.query(sql);
		try {
			while(rs.next()){
				HotNewsVO vo = new HotNewsVO();
				vo.setNewsID(rs.getInt("stuNewsID"));
				vo.setNewsTitle(rs.getString("stuNewsTitle"));
				list.add(vo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close(dao,rs);
		}
		return list;
	}
	
	/**
	 * 更新栏目信息
	 * @param name
	 * @param ID
	 * @param remark
	 */
	public void updateType(String[] name,String[] ID,String[] remark){		
		Dao dao = new Dao();
		ResultSet rs = null;
		for(int i=0;i<name.length;i++){
			String sql = "update NewsType set typeName='"+name[i]+"',remark='"+remark[i]+"' where typeID="+ID[i];
			dao.execute(sql);
		}
		
		close(dao,rs);
	}
	
	/**
	 * 获取这栏目下的其它文章信息
	 * @param vo
	 * @return
	 */
	public List<HotNewsVO> getotherNews(HotNewsVO vo){
		String sql = "select * from StuNews where stuNewsID<>"+vo.getNewsID()+" and typeID="+vo.getTypeID()+" order by publishTime desc limit 0,8";
		List<HotNewsVO> list = new ArrayList<HotNewsVO>();
		Dao dao = new Dao();
		ResultSet rs = null;
		rs=dao.query(sql);
		try {
			while(rs.next()){
				HotNewsVO hnvo = new HotNewsVO();
				hnvo.setNewsID(rs.getInt("stuNewsID"));
				hnvo.setNewsTitle(rs.getString("stuNewsTitle"));
				hnvo.setPublishTime(sdf.format(rs.getDate("publishTime")));
				list.add(hnvo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close(dao,rs);
		}
		return list;
	}
	
	/**
	 * 关闭连接
	 *
	 */
	public void close(Dao dao,ResultSet rs){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			dao.close();
			
		}
	}
}
